﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using KP_CommonLibrary.KP_ObjectReferences;

namespace KP_CommonLibrary.KP_DataAccess
{
    public class KP_DALVoucher : KP_DALBase
    {
        public string MasterTable { get; set; }
        public string DetailTable { get; set; }
        public string MasterInsert { get; set; }
        public string MasterUpdate { get; set; }
        public string MasterDelete { get; set; }
        public string DetailInsert { get; set; }
        public string DetailUpdate { get; set; }
        public string DetailDelete { get; set; }
        public string DetailSelect { get; set; }
        private Dictionary<string, string> dicKeyMaps;
        public Dictionary<string, string> KeyMaps
        {
            get
            {
                if (dicKeyMaps == null)
                {
                    dicKeyMaps = new Dictionary<string, string>();
                }
                return dicKeyMaps;
            }
            set
            {
                dicKeyMaps = value;
            }
        }

        public KP_DALVoucher()
        {
        }

        public virtual DataTable SelectDetail(DataRow masterRow)
        {
            return SqlHelper.ExecuteDataTable(KP_OCnnSql.Cnn_String, DetailSelect, GenarateData4SelectDetail(masterRow));
        }
        private DataRow GenarateData4SelectDetail(DataRow row)
        {
            DataTable tblTemp = row.Table.Clone();
            tblTemp.ImportRow(row);
            string[] allKeys = KeyMaps.Keys.ToArray<string>();
            for (int i = 0; i < allKeys.Length; i++)
            {
                string detailColumn = KeyMaps[allKeys[i]];
                if (tblTemp.Columns.Contains(detailColumn))
                    continue;
                else
                {
                    DataColumn col = new DataColumn(detailColumn, tblTemp.Columns[allKeys[i]].DataType);
                    tblTemp.Columns.Add(col);
                    tblTemp.Rows[0][col] = tblTemp.Rows[0][allKeys[i]];
                }
            }
            return tblTemp.Rows[0];
        }

        public KP_DALVoucher(string master, string detail)
        {
            MasterTable = master;
            DetailTable = detail;
        }
        public virtual void Validate(ValidateMode mode, DataRow master, DataView detail)
        {
            //return true;
        }
        public virtual bool Insert(SqlTransaction transaction, DataRow master, DataTable detail)
        {

            SqlConnection connection = null;
            SqlTransaction vTransaction = null;
            try
            {
                Validate(ValidateMode.Update, master, detail.DefaultView);
                if (transaction == null)
                {
                    connection = new SqlConnection(KP_OCnnSql.Cnn_String);
                    connection.Open();
                    vTransaction = connection.BeginTransaction();
                    transaction = vTransaction;
                }
                BeforePerformAction(transaction, master, detail.DefaultView, ValidateMode.Insert);
                SqlHelper.ExecuteNonQuery(transaction, MasterInsert, master);

                for (int i = 0; i < detail.Rows.Count; i++)
                {
                    DataRow currentDetail = detail.Rows[i];
                    string[] allKey = KeyMaps.Keys.ToArray<string>();
                    for (int j = 0; j < allKey.Length; j++)
                    {
                        currentDetail[KeyMaps[allKey[j]]] = master[allKey[j]];
                    }

                    SqlHelper.ExecuteNonQuery(transaction, DetailInsert, currentDetail);


                }
                AfterPerformAction(transaction, master, detail.DefaultView, ValidateMode.Insert);
                if (vTransaction != null)
                {
                    vTransaction.Commit();
                    vTransaction = null;
                }
            }
            catch (Exception ex)
            {
                if (vTransaction != null)
                {
                    vTransaction.Rollback();
                    vTransaction = null;
                    return false;
                }
                else
                    throw ex;

            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                    connection.Close();
            }
            return true;

        }
        public virtual bool Insert(SqlTransaction transaction, DataRow master, DataView detail)
        {

            SqlConnection connection = null;
            SqlTransaction vTransaction = null;
            try
            {
                Validate(ValidateMode.Update, master, detail);
                if (transaction == null)
                {
                    connection = new SqlConnection(KP_OCnnSql.Cnn_String);
                    connection.Open();
                    vTransaction = connection.BeginTransaction();
                    transaction = vTransaction;
                }
                BeforePerformAction(transaction, master, detail, ValidateMode.Insert);
                SqlHelper.ExecuteNonQuery(transaction, MasterInsert, master);

                for (int i = 0; i < detail.Count; i++)
                {
                    DataRow currentDetail = detail[i].Row;
                    string[] allKey = KeyMaps.Keys.ToArray<string>();
                    for (int j = 0; j < allKey.Length; j++)
                    {
                        currentDetail[KeyMaps[allKey[j]]] = master[allKey[j]];
                    }

                    SqlHelper.ExecuteNonQuery(transaction, DetailInsert, currentDetail);


                }
                AfterPerformAction(transaction, master, detail, ValidateMode.Insert);
                if (vTransaction != null)
                {
                    vTransaction.Commit();
                    vTransaction = null;
                }
            }
            catch (Exception ex)
            {
                if (vTransaction != null)
                {
                    vTransaction.Rollback();
                    vTransaction = null;
                    return false;
                }
                else
                    throw ex;

            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                    connection.Close();
            }
            return true;

        }

        public virtual bool Update(SqlTransaction transaction, DataRow master, DataTable detail)
        {
            SqlConnection connection = null;
            SqlTransaction vTransaction = null;
            try
            {
                Validate(ValidateMode.Update, master, detail.DefaultView);
                if (transaction == null)
                {
                    connection = new SqlConnection(KP_OCnnSql.Cnn_String);
                    connection.Open();
                    vTransaction = connection.BeginTransaction();
                    transaction = vTransaction;
                }
                BeforePerformAction(transaction, master, detail.DefaultView, ValidateMode.Update);
                SqlHelper.ExecuteNonQuery(transaction, MasterUpdate, master);

                for (int i = 0; i < detail.Rows.Count; i++)
                {
                    DataRow currentDetail = detail.Rows[i];
                    switch (currentDetail.RowState)
                    {
                        case DataRowState.Added:
                            // Insert
                            string[] allKey = KeyMaps.Keys.ToArray<string>();
                            for (int j = 0; j < allKey.Length; j++)
                            {
                                currentDetail[KeyMaps[allKey[j]]] = master[allKey[j]];
                            }
                            SqlHelper.ExecuteNonQuery(transaction, DetailInsert, currentDetail);
                            break;
                        case DataRowState.Deleted:
                            //Delete
                            SqlHelper.ExecuteNonQuery(transaction, DetailDelete, currentDetail);

                            break;
                        case DataRowState.Modified:

                            //Update
                            SqlHelper.ExecuteNonQuery(transaction, DetailUpdate, currentDetail);
                            break;
                        case DataRowState.Unchanged:
                            //Nothing change
                            break;
                        case DataRowState.Detached:
                            //new Row but not added to table--> nothing 
                            break;
                    }

                }
                AfterPerformAction(transaction, master, detail.DefaultView, ValidateMode.Update);
                if (vTransaction != null)
                {
                    vTransaction.Commit();
                    vTransaction = null;
                }
                return true;
            }
            catch (Exception ex)
            {
                if (vTransaction != null)
                {
                    vTransaction.Rollback();
                    vTransaction = null;
                    return false;
                }
                else
                    throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            //return false;
        }

        public virtual bool Update(SqlTransaction transaction, DataRow master, DataView detail)
        {
            SqlConnection connection = null;
            SqlTransaction vTransaction = null;
            try
            {
                Validate(ValidateMode.Update, master, detail);
                if (transaction == null)
                {
                    connection = new SqlConnection(KP_OCnnSql.Cnn_String);
                    connection.Open();
                    vTransaction = connection.BeginTransaction();
                    transaction = vTransaction;
                }
                BeforePerformAction(transaction, master, detail, ValidateMode.Update);
                SqlHelper.ExecuteNonQuery(transaction, MasterUpdate, master);

                for (int i = 0; i < detail.Count; i++)
                {
                    DataRow currentDetail = detail[i].Row;
                    switch (currentDetail.RowState)
                    {
                        case DataRowState.Added:
                            // Insert
                            string[] allKey = KeyMaps.Keys.ToArray<string>();
                            for (int j = 0; j < allKey.Length; j++)
                            {
                                currentDetail[KeyMaps[allKey[j]]] = master[allKey[j]];
                            }
                            SqlHelper.ExecuteNonQuery(transaction, DetailInsert, currentDetail);
                            break;
                        case DataRowState.Deleted:
                            //Delete
                            SqlHelper.ExecuteNonQuery(transaction, DetailDelete, currentDetail);

                            break;
                        case DataRowState.Modified:

                            //Update
                            SqlHelper.ExecuteNonQuery(transaction, DetailUpdate, currentDetail);
                            break;
                        case DataRowState.Unchanged:
                            //Nothing change
                            break;
                        case DataRowState.Detached:
                            //new Row but not added to table--> nothing 
                            break;
                    }

                }
                AfterPerformAction(transaction, master, detail, ValidateMode.Update);
                if (vTransaction != null)
                {
                    vTransaction.Commit();
                    vTransaction = null;
                }
                return true;
            }
            catch (Exception ex)
            {
                if (vTransaction != null)
                {
                    vTransaction.Rollback();
                    vTransaction = null;
                    return false;
                }
                else
                    throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            //return false;
        }

        public virtual bool Delete(SqlTransaction transaction, DataRow master, DataTable detail, bool childreDelete)
        {
            SqlConnection connection = null;
            SqlTransaction vTransaction = null;
            try
            {
                Validate(ValidateMode.Update, master, detail.DefaultView);
                if (transaction == null)
                {
                    connection = new SqlConnection(KP_OCnnSql.Cnn_String);
                    connection.Open();
                    vTransaction = connection.BeginTransaction();
                    transaction = vTransaction;
                }

                BeforePerformAction(transaction, master, detail.DefaultView, ValidateMode.Delete);
                if (childreDelete)
                    for (int i = 0; i < detail.Rows.Count; i++)
                    {
                        DataRow currentDetail = detail.Rows[i];
                        SqlHelper.ExecuteNonQuery(transaction, DetailDelete, currentDetail);
                    }
                SqlHelper.ExecuteNonQuery(transaction, MasterUpdate, master);
                AfterPerformAction(transaction, master, detail.DefaultView, ValidateMode.Delete);

                if (vTransaction != null)
                {
                    vTransaction.Commit();
                    vTransaction = null;
                }
                return true;
            }
            catch (Exception ex)
            {
                if (vTransaction != null)
                {
                    vTransaction.Rollback();
                    vTransaction = null;
                    return false;
                }
                else
                    throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
           // return false;

        }

        public virtual bool Delete(SqlTransaction transaction, DataRow master, DataView detail, bool childreDelete)
        {
            SqlConnection connection = null;
            SqlTransaction vTransaction = null;
            try
            {
                Validate(ValidateMode.Update, master, detail);
                if (transaction == null)
                {
                    connection = new SqlConnection(KP_OCnnSql.Cnn_String);
                    connection.Open();
                    vTransaction = connection.BeginTransaction();
                    transaction = vTransaction;
                }

                BeforePerformAction(transaction, master, detail, ValidateMode.Delete);
                if (childreDelete)
                    for (int i = 0; i < detail.Count; i++)
                    {
                        DataRow currentDetail = detail[i].Row;
                        SqlHelper.ExecuteNonQuery(transaction, DetailDelete, currentDetail);
                    }
                SqlHelper.ExecuteNonQuery(transaction, MasterUpdate, master);
                AfterPerformAction(transaction, master, detail, ValidateMode.Delete);

                if (vTransaction != null)
                {
                    vTransaction.Commit();
                    vTransaction = null;
                }
                return true;
            }
            catch (Exception ex)
            {
                if (vTransaction != null)
                {
                    vTransaction.Rollback();
                    vTransaction = null;
                    return false;
                }
                else
                    throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            // return false;

        }
        protected virtual void BeforeMasterAction(SqlTransaction transaction, DataRow master, ValidateMode mode)
        {

        }
        protected virtual void AfterMasterAction(SqlTransaction transaction, DataRow master, ValidateMode mode)
        {

        }
        protected virtual void AfterDetailAction(SqlTransaction transaction, DataRow detail, ValidateMode mode)
        {

        }
        protected virtual void BeforeDetailAction(SqlTransaction transaction, DataRow detail, ValidateMode mode)
        {

        }
        protected virtual void BeforePerformAction(SqlTransaction transaction, DataRow master, DataView detail, ValidateMode mode)
        {
        }
        protected virtual void AfterPerformAction(SqlTransaction transaction, DataRow master, DataView detail, ValidateMode mode)
        {
        }

        public DataTable GetMasterSchema()
        {
            return KP_DALBase.GetSchema(MasterTable);
        }
        public DataTable GetDetailSchema()
        {
            return KP_DALBase.GetSchema(DetailTable);
        }

        public void Test()
        {
            SqlCommandBuilder buulder = new SqlCommandBuilder();

        }
    }
}
